# =========================== ASSET INDEX (with MP join) ===========================
# Minimal deps
library(readxl)
library(dplyr)
library(tidyr)
library(readr)
# lightweight DBF reader (recommended/core R): only used if available
suppressWarnings({
  has_foreign <- requireNamespace("foreign", quietly = TRUE)
})

if (!requireNamespace("here", quietly = TRUE)) install.packages("here", quiet = TRUE)
# -------------------------------------------------------------------
# Project base directory (no hard-coded paths)
# -------------------------------------------------------------------
if (!requireNamespace("here", quietly = TRUE)) install.packages("here", quiet = TRUE)
base_dir <- normalizePath(here::here(), winslash = "/")

# -------------------------------------------------------------------
# Standardized paths
# -------------------------------------------------------------------
in_dir   <- file.path(base_dir, "Data", "Economic", "Census_2011")
build_dir <- file.path(base_dir, "Processed_Data", "Buildings")
sp_shp    <- file.path(base_dir, "Data", "Shape_Files", "SP_SA_2011", "SP_SA_2011.shp")
od_dir    <- file.path(base_dir, "Data", "OD_Matrices")
out_dir   <- file.path(base_dir, "Processed_Data", "BD")

dir.create(out_dir, recursive = TRUE, showWarnings = FALSE)


# ---- SP→MP crosswalk (SP_CODE, MP_CODE, MP_NAME) --------------------------------
# Preferred: read from shapefile DBF to avoid GIS deps; fallback: CSV crosswalk.
get_sp_mp_cw <- function(base_dir) {
  # 1) Try DBF from the subplace shapefile
  dbf_path <- file.path(base_dir, "Data", "Shape_Files", "SP_SA_2011", "SP_SA_2011.dbf")
  if (file.exists(dbf_path) && has_foreign) {
    cw <- foreign::read.dbf(dbf_path, as.is = TRUE)
    # Standard field names commonly present in Stats SA subplace shapefile:
    # SP_CODE (or SPCODE), MP_CODE (or MPCODE), MP_NAME
    nm <- names(cw)
    sp_col <- dplyr::first(intersect(nm, c("SP_CODE","SPCODE","sp_code")))
    mp_code_col <- dplyr::first(intersect(nm, c("MP_CODE","MPCODE","mp_code")))
    sp_name_col <- dplyr::first(intersect(nm, c("SP_NAME")))
    mp_name_col <- dplyr::first(intersect(nm, c("MP_NAME","mp_name","MAINPLACE","MainPlace")))
    if (is.null(sp_col) || is.null(mp_code_col) || is.null(mp_name_col)) {
      stop("SP/MP columns not found in DBF. Found columns: ", paste(nm, collapse = ", "))
    }
    cw <- cw |>
      transmute(
        SP_CODE  = suppressWarnings(as.integer(.data[[sp_col]])),
        MP_CODE  = as.character(.data[[mp_code_col]]),
        SP_NAME  = as.character(.data[[sp_name_col]]),
        MP_NAME  = as.character(.data[[mp_name_col]])
      ) |>
      distinct() |>
      filter(!is.na(SP_CODE))
    return(cw)
  }
  
  # 2) Try a CSV crosswalk if you’ve exported one before
  csv_cands <- c(
    file.path(base_dir, "Processed_Data", "Boundaries", "subplace_mp_crosswalk.csv"),
    file.path(base_dir, "Data", "Shape_Files", "SP_SA_2011", "SP_SA_2011_crosswalk.csv")
  )
  csv_hit <- csv_cands[file.exists(csv_cands)][1]
  if (!is.na(csv_hit)) {
    cw <- readr::read_csv(csv_hit, show_col_types = FALSE)
    need <- c("SP_CODE","SP_NAME", "MP_CODE","MP_NAME")
    miss <- setdiff(need, names(cw))
    if (length(miss)) stop("Crosswalk CSV missing: ", paste(miss, collapse=", "), " (", csv_hit, ")")
    cw <- cw |>
      mutate(SP_CODE = as.integer(SP_CODE),
             MP_CODE = as.character(MP_CODE),
             SP_NAME = as.character(SP_NAME),
             MP_NAME = as.character(MP_NAME)) |>
      distinct() |>
      filter(!is.na(SP_CODE))
    return(cw)
  }
  
  stop(
    "No SP→MP crosswalk found.\n",
    "Either ensure '", dbf_path, "' exists (recommended; uses foreign::read.dbf),\n",
    "or provide a CSV with columns SP_CODE, MP_CODE, MP_NAME at one of:\n  - ",
    paste(csv_cands, collapse = "\n  - "),
    "\n(If needed, you can export from the shapefile once with sf::st_read(...) |> st_drop_geometry() |> select(SP_CODE, MP_CODE, MP_NAME) |> write_csv(...))."
  )
}

# tiny helper used in every block: "-"→NA, numeric, NA→0
norm_cols <- function(df, cols) {
  df |>
    mutate(across(all_of(cols),
                  ~ suppressWarnings(as.numeric(replace(as.character(.), . == "-", NA))))) |>
    mutate(across(all_of(cols), ~ replace_na(., 0)))
}
cap1 <- function(x) pmin(x, 1)

# -------------------------------------------------------------------
# COOKING.xlsx  → sh_modern_cooking
# -------------------------------------------------------------------
cooking <- read_excel(file.path(in_dir, "Cooking.xlsx"))
cooking <- cooking |>
  norm_cols(c("Electricity","Gas","Solar","Total")) |>
  mutate(
    sh_modern_cooking = (Electricity + Gas + Solar) / Total,
    sh_modern_cooking = cap1(sh_modern_cooking),
    sh_modern_cooking = if_else(Total < 20, NA_real_, sh_modern_cooking),
    SP_CODE = as.integer(SP_CODE)
  ) |>
  select(SP_CODE, sh_modern_cooking)
saveRDS(cooking, file.path(out_dir, "cooking.rds"))

# -------------------------------------------------------------------
# Housing.xlsx  → sh_modern_housing  (1 - primitive)
# -------------------------------------------------------------------
housing <- read_excel(file.path(in_dir, "Housing.xlsx"))
housing <- housing |>
  norm_cols(c("Traditional dwelling/hut/structure made of traditional materials",
              "Informal dwelling (shack; in backyard)",
              "Informal dwelling (shack; not in backyard; e.g. in an informal/squatter settlement or on a farm)",
              "Caravan/tent", "Other", "Unspecified", "Total")) |>
  mutate(
    sh_primitive_housing =
      (`Traditional dwelling/hut/structure made of traditional materials` +
         `Informal dwelling (shack; in backyard)` +
         `Informal dwelling (shack; not in backyard; e.g. in an informal/squatter settlement or on a farm)` +
         `Caravan/tent` + Other + Unspecified) / Total,
    sh_primitive_housing = cap1(sh_primitive_housing),
    sh_primitive_housing = if_else(Total < 20, NA_real_, sh_primitive_housing),
    sh_modern_housing    = if_else(Total < 20, NA_real_, 1 - sh_primitive_housing),
    SP_CODE = as.integer(SP_CODE)
  ) |>
  select(SP_CODE, sh_modern_housing)
saveRDS(housing, file.path(out_dir, "housing.rds"))

# -------------------------------------------------------------------
# Lighting.xlsx  → sh_modern_lighting
# -------------------------------------------------------------------
lighting <- read_excel(file.path(in_dir, "Lighting.xlsx"))
lighting <- lighting |>
  norm_cols(c("Electricity","Gas","Solar","Total")) |>
  mutate(
    sh_modern_lighting = (Electricity + Gas + Solar) / Total,
    sh_modern_lighting = cap1(sh_modern_lighting),
    sh_modern_lighting = if_else(Total < 20, NA_real_, sh_modern_lighting),
    SP_CODE = as.integer(SP_CODE)
  ) |>
  select(SP_CODE, sh_modern_lighting)
saveRDS(lighting, file.path(out_dir, "lighting.rds"))

# -------------------------------------------------------------------
# Pipedwater.xlsx  → sh_modern_piped  (inside dwelling/institution)
# -------------------------------------------------------------------
piped <- read_excel(file.path(in_dir, "Pipedwater.xlsx"))
piped <- piped |>
  norm_cols(c("Piped (tap) water inside dwelling/institution", "Total")) |>
  mutate(
    sh_modern_piped = `Piped (tap) water inside dwelling/institution` / Total,
    sh_modern_piped = cap1(sh_modern_piped),
    sh_modern_piped = if_else(Total < 20, NA_real_, sh_modern_piped),
    SP_CODE = as.integer(SP_CODE)
  ) |>
  select(SP_CODE, sh_modern_piped)
saveRDS(piped, file.path(out_dir, "piped.rds"))

# -------------------------------------------------------------------
# Refuse_Removal.xlsx  → sh_modern_refuse
# -------------------------------------------------------------------
refuse <- read_excel(file.path(in_dir, "Refuse_Removal.xlsx"))
refuse <- refuse |>
  norm_cols(c("Removed by local authority/private company at least once a week", "Total")) |>
  mutate(
    sh_modern_refuse = `Removed by local authority/private company at least once a week` / Total,
    sh_modern_refuse = cap1(sh_modern_refuse),
    sh_modern_refuse = if_else(Total < 20, NA_real_, sh_modern_refuse),
    SP_CODE = as.integer(SP_CODE)
  ) |>
  select(SP_CODE, sh_modern_refuse)
saveRDS(refuse, file.path(out_dir, "refuse.rds"))

# -------------------------------------------------------------------
# Toilet.xlsx  → sh_modern_toilet
# -------------------------------------------------------------------
toilet <- read_excel(file.path(in_dir, "Toilet.xlsx"))
toilet <- toilet |>
  norm_cols(c("Flush toilet (connected to sewerage system)",
              "Flush toilet (with septic tank)",
              "Chemical toilet", "Total")) |>
  mutate(
    sh_modern_toilet = (`Flush toilet (connected to sewerage system)` +
                          `Flush toilet (with septic tank)` +
                          `Chemical toilet`) / Total,
    sh_modern_toilet = cap1(sh_modern_toilet),
    sh_modern_toilet = if_else(Total < 20, NA_real_, sh_modern_toilet),
    SP_CODE = as.integer(SP_CODE)
  ) |>
  select(SP_CODE, sh_modern_toilet)
saveRDS(toilet, file.path(out_dir, "toilet.rds"))

# -------------------------------------------------------------------
# tv.xlsx / fridge.xlsx / washing_machine.xlsx / motor_vehicle.xlsx /
# vacuum.xlsx / computer.xlsx → shares = Yes/Total
# -------------------------------------------------------------------
tv <- read_excel(file.path(in_dir, "tv.xlsx")) |>
  norm_cols(c("Yes","Total")) |>
  mutate(sh_tv = Yes/Total,
         sh_tv = cap1(sh_tv),
         sh_tv = if_else(Total < 20, NA_real_, sh_tv),
         SP_CODE = as.integer(SP_CODE)) |>
  select(SP_CODE, sh_tv)
saveRDS(tv, file.path(out_dir, "tv.rds"))

fridge <- read_excel(file.path(in_dir, "fridge.xlsx")) |>
  norm_cols(c("Yes","Total")) |>
  mutate(sh_fridge = Yes/Total,
         sh_fridge = cap1(sh_fridge),
         sh_fridge = if_else(Total < 20, NA_real_, sh_fridge),
         SP_CODE = as.integer(SP_CODE)) |>
  select(SP_CODE, sh_fridge)
saveRDS(fridge, file.path(out_dir, "fridge.rds"))

washing <- read_excel(file.path(in_dir, "washing_machine.xlsx")) |>
  norm_cols(c("Yes","Total")) |>
  mutate(sh_washing_machine = Yes/Total,
         sh_washing_machine = cap1(sh_washing_machine),
         sh_washing_machine = if_else(Total < 20, NA_real_, sh_washing_machine),
         SP_CODE = as.integer(SP_CODE)) |>
  select(SP_CODE, sh_washing_machine)
saveRDS(washing, file.path(out_dir, "washing_machine.rds"))

vehicle <- read_excel(file.path(in_dir, "motor_vehicle.xlsx")) |>
  norm_cols(c("Yes","Total")) |>
  mutate(sh_motor_vehicle = Yes/Total,
         sh_motor_vehicle = cap1(sh_motor_vehicle),
         sh_motor_vehicle = if_else(Total < 20, NA_real_, sh_motor_vehicle),
         SP_CODE = as.integer(SP_CODE)) |>
  select(SP_CODE, sh_motor_vehicle)
saveRDS(vehicle, file.path(out_dir, "motor_vehicle.rds"))

vacuum <- read_excel(file.path(in_dir, "vacuum.xlsx")) |>
  norm_cols(c("Yes","Total")) |>
  mutate(sh_vacuum = Yes/Total,
         sh_vacuum = cap1(sh_vacuum),
         sh_vacuum = if_else(Total < 20, NA_real_, sh_vacuum),
         SP_CODE = as.integer(SP_CODE)) |>
  select(SP_CODE, sh_vacuum)
saveRDS(vacuum, file.path(out_dir, "vacuum.rds"))

computer <- read_excel(file.path(in_dir, "computer.xlsx")) |>
  norm_cols(c("Yes","Total")) |>
  mutate(sh_computer = Yes/Total,
         sh_computer = cap1(sh_computer),
         sh_computer = if_else(Total < 20, NA_real_, sh_computer),
         SP_CODE = as.integer(SP_CODE)) |>
  select(SP_CODE, sh_computer)
saveRDS(computer, file.path(out_dir, "computer.rds"))

# -------------------------------------------------------------------
# education_attainment_20.xlsx → sh_tertiary_completed  (patched)
# -------------------------------------------------------------------
edu <- read_excel(file.path(in_dir, "education_attainment_20.xlsx"))
names(edu) <- trimws(names(edu))  # fix trailing spaces
edu <- edu |>
  norm_cols(c("Some primary education",
              "Primary education completed",
              "Some secondary education",
              "Secondary education completed",
              "Other, e.g. NTC",
              "Certificate with Grade 12 / Std 10",
              "Tertiary education completed",
              "No schooling",
              "Unspecified")) |>
  mutate(
    Total_calc = `Some primary education` + `Primary education completed` +
      `Some secondary education` + `Secondary education completed` +
      `Other, e.g. NTC` + `Certificate with Grade 12 / Std 10` +
      `Tertiary education completed` + `No schooling` + `Unspecified`,
    sh_tertiary_completed = `Tertiary education completed` / Total_calc,
    sh_tertiary_completed = cap1(sh_tertiary_completed),
    sh_tertiary_completed = if_else(Total_calc < 20, NA_real_, sh_tertiary_completed),
    SP_CODE = as.integer(SP_CODE)
  ) |>
  select(SP_CODE, sh_tertiary_completed)
saveRDS(edu, file.path(out_dir, "sh_tertiary_completed.rds"))

# -------------------------------------------------------------------
# Employment.xlsx → employed_share
# -------------------------------------------------------------------
emp <- read_excel(file.path(in_dir, "Employment.xlsx"))
emp <- emp |>
  norm_cols(c("Employed","Total")) |>
  mutate(
    employed_share = Employed / Total,
    employed_share = cap1(employed_share),
    employed_share = if_else(Total < 20, NA_real_, employed_share),
    SP_CODE = as.integer(SP_CODE)
  ) |>
  select(SP_CODE, employed_share)
saveRDS(emp, file.path(out_dir, "employment.rds"))

# -------------------------------------------------------------------
# Merge and indices
# -------------------------------------------------------------------
merged <- housing |>
  full_join(cooking, by = "SP_CODE") |>
  full_join(toilet,  by = "SP_CODE") |>
  full_join(refuse,  by = "SP_CODE") |>
  full_join(piped,   by = "SP_CODE") |>
  full_join(lighting,by = "SP_CODE") |>
  full_join(tv,      by = "SP_CODE") |>
  full_join(fridge,  by = "SP_CODE") |>
  full_join(washing, by = "SP_CODE") |>
  full_join(vehicle, by = "SP_CODE") |>
  full_join(vacuum,  by = "SP_CODE") |>
  full_join(computer,by = "SP_CODE") |>
  full_join(edu,     by = "SP_CODE") |>
  full_join(emp,     by = "SP_CODE")

asset_inputs <- merged |>
  mutate(
    housing_index          = sh_modern_housing,
    energy_index           = (sh_modern_cooking + sh_modern_lighting) / 2,
    water_sanitation_index = (sh_modern_toilet + sh_modern_refuse + sh_modern_piped) / 3,
    durable_goods_index    = (sh_tv + sh_fridge + sh_washing_machine + sh_motor_vehicle + sh_vacuum + sh_computer) / 6,
    education_index        = sh_tertiary_completed,
    asset_index            = (housing_index + energy_index + water_sanitation_index +
                                durable_goods_index + education_index) / 5
  ) |>
  arrange(SP_CODE)

# ---- Attach MP_CODE / MP_NAME ----
sp_mp <- get_sp_mp_cw(base_dir)
asset_inputs <- asset_inputs |>
  left_join(sp_mp, by = "SP_CODE") |>
  relocate(SP_CODE, SP_NAME, MP_CODE, MP_NAME, .before = everything())

# -------------------------------------------------------------------
# Save
# -------------------------------------------------------------------
saveRDS(asset_inputs, file.path(out_dir, "asset_index.rds"))
readr::write_csv(asset_inputs, file.path(out_dir, "asset_index.csv"))
cat("Done. Wrote asset_index.[rds,csv] with MP_CODE/MP_NAME to: ", out_dir, "\n")

